package db.local;

import static common.utils.CommonUtils.trimStatements;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import org.apache.tools.ant.util.FileUtils;

import db.manager.DBManager;

public class DBLocal {

	private static final String SCHEMA_SQL_FILE = "config/build_schema.sql";
	private static final String SCHEMA_CLEANUP_FILE = "config/drop_schema_tables.sql";
	private static final String QUESTION_TEMPLATES_FILE = "config/question_templates";
	private static final String CHARACTER_PROPERTIES_FILE = "config/character_properties";
	private static final String STATEMENT_SEPARATOR = ";";

	private static DBLocal local;

	private Connection connection = null;
	private DBManager dbManager;
	
	private String[] QuestionTemplates;
	private String[] CharacterProperties;

	private DBLocal() {
	    dbManager = DBManager.getInstance();
	}

	public static DBLocal getInstance() {
		if (local == null) {
			local = new DBLocal();
		}
		return local;
	}	

    public void executeStatementsBatchFromFile(String sqlStatmentsFileName, String errorMessage, boolean throwSqlException) throws  IOException, SQLException {
        File file = new File(sqlStatmentsFileName);
        InputStream is = new FileInputStream(file);
        String sqlScript = FileUtils.readFully(new InputStreamReader(is));
        is.close();
        String[] statements = trimStatements(sqlScript.split(STATEMENT_SEPARATOR));
        Connection connection = dbManager.poolConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            for (String sqlStatement : statements) {
                if (!sqlStatement.trim().isEmpty()) {
                    statement.addBatch(sqlStatement);
                }
            }

            int[] results = statement.executeBatch();
            for (int i=0; i<results.length; i++) {
                if (results[i] == Statement.EXECUTE_FAILED) {
                    if (throwSqlException){
                        throw new SQLException(errorMessage);
                    }
                }
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
            dbManager.returnConnection(connection);
        }       
    }
	
	public void createTables(boolean dropOldTables) throws SQLException, IOException {
		if (dropOldTables) {
			dropTables();
		}
		executeStatementsBatchFromFile(SCHEMA_SQL_FILE, "Failed creating schema",true);
	}
	
    public void dropTables() throws SQLException, IOException {
        executeStatementsBatchFromFile(SCHEMA_CLEANUP_FILE, null, false);
    }

	public void readQuestionTemplatesFile(String questionTemplatesFileName) throws  IOException {
        File file = new File(questionTemplatesFileName);
        InputStream is = new FileInputStream(file);
		String templates = FileUtils.readFully(new InputStreamReader(is));
		is.close();
		
		QuestionTemplates = trimStatements(templates.split(STATEMENT_SEPARATOR));
	}
	
	public void readAskedAboutPropertiesFile(String fileName) throws  IOException {
        File file = new File(fileName);
        InputStream is = new FileInputStream(file);
		String properties = FileUtils.readFully(new InputStreamReader(is));
		is.close();
		
		CharacterProperties = trimStatements(properties.split(STATEMENT_SEPARATOR));
	}	
	
	public void propertiesTableInitialPopulation() throws SQLException, IOException  {
		
		readQuestionTemplatesFile(QUESTION_TEMPLATES_FILE);		
		readAskedAboutPropertiesFile(CHARACTER_PROPERTIES_FILE);
		Map<String,String> propertyToTemplateMatching = new HashMap<String,String>();
		
		propertyToTemplateMatching.put(CharacterProperties[0], QuestionTemplates[0]);

		propertyToTemplateMatching.put(CharacterProperties[0], QuestionTemplates[0]);     //gender: Is your character <0> ?
		propertyToTemplateMatching.put(CharacterProperties[1], QuestionTemplates[1]);     //height: Is your character's height <0> ?
		propertyToTemplateMatching.put(CharacterProperties[2], QuestionTemplates[2]);     //weight: Is your character's weight <0> ?
		propertyToTemplateMatching.put(CharacterProperties[3], QuestionTemplates[3]);     //CharacterDateOfBirth: Was your character born <0> ?
		propertyToTemplateMatching.put(CharacterProperties[4], QuestionTemplates[4]);     //BasedOn: Is your character based on <0> ?
		propertyToTemplateMatching.put(CharacterProperties[5], QuestionTemplates[5]);     //Quotation: Does your character say "<0>" ?
		propertyToTemplateMatching.put(CharacterProperties[6], QuestionTemplates[6]);     //Objects: Does your character possess <0> ?
		propertyToTemplateMatching.put(CharacterProperties[7], QuestionTemplates[0]);     //Species: 
		propertyToTemplateMatching.put(CharacterProperties[8], QuestionTemplates[7]);     //Occupation: Is your character's occupation: <0> ?
		propertyToTemplateMatching.put(CharacterProperties[9], QuestionTemplates[0]);     //Ranks: 
		propertyToTemplateMatching.put(CharacterProperties[10], QuestionTemplates[8]);     //Organization: Does your character belong to the <0> ?
		propertyToTemplateMatching.put(CharacterProperties[11], QuestionTemplates[9]);     //Powers: Does your character have the ability of <0> ?
		propertyToTemplateMatching.put(CharacterProperties[12], QuestionTemplates[10]);     //Universe: Is your character from <0> ?
		propertyToTemplateMatching.put(CharacterProperties[13], QuestionTemplates[11]);     //CreatedBy: Was your character created by <0> ?
		propertyToTemplateMatching.put(CharacterProperties[14], QuestionTemplates[0]);     //Ethnicity: 
		propertyToTemplateMatching.put(CharacterProperties[15], QuestionTemplates[12]);     //School: Did your character go to <0> ?
		propertyToTemplateMatching.put(CharacterProperties[16], QuestionTemplates[13]);     //MedicalCondition: Does your character suffer from <0> ?
		propertyToTemplateMatching.put(CharacterProperties[17], QuestionTemplates[10]);     //CharacterPlacesLived: 
		propertyToTemplateMatching.put(CharacterProperties[18], QuestionTemplates[14]);     //CharacterPlaceOfBirth: Was your character born in <0> ?
		propertyToTemplateMatching.put(CharacterProperties[19], QuestionTemplates[15]);     //Employer: Was your character employed by <0> ?
		propertyToTemplateMatching.put(CharacterProperties[20], QuestionTemplates[16]);     //CharacterParent: Is your character the parent of <0> ?
		propertyToTemplateMatching.put(CharacterProperties[21], QuestionTemplates[17]);     //CharacterChildren: Is your character the child of <0> ?
		propertyToTemplateMatching.put(CharacterProperties[22], QuestionTemplates[18]);     //CharacterMarriedTo: was your character married to <0> ?
		propertyToTemplateMatching.put(CharacterProperties[23], QuestionTemplates[19]);     //CharacterRomanticallyInvolvedWith: was your character romantically involved with <0> ?
		propertyToTemplateMatching.put(CharacterProperties[24], QuestionTemplates[20]);     //CharacterSibling: Is your character a sibling of <0> ?
		
		connection = dbManager.poolConnection();

		PreparedStatement preparedStatement	= connection.prepareStatement(
				"INSERT INTO Properties(propertyName, questionTemplate) VALUES(?,?)" );
		
		for (Map.Entry<String, String> entry : propertyToTemplateMatching.entrySet()){
			preparedStatement.setString(1, entry.getKey());
			preparedStatement.setString(2,  entry.getValue());
			preparedStatement.addBatch();
		}
	
		try {
			int[] results = preparedStatement.executeBatch();

			for (int i=0; i<results.length; i++) {
				if (results[i] != 1) {
					throw new SQLException("Failed To Execute Prepared Statments Batch ");
				}
			}
		} finally {
			if (preparedStatement != null) {
			    preparedStatement.close();
			}
			dbManager.returnConnection(connection);		
		}
	}
}
